package org.teachertools.services;

import java.util.List;

import javax.persistence.PersistenceException;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.teachertools.common.ValidationUtils;
import org.teachertools.data.Competence;
import org.teachertools.services.exception.ServiceException;

@Repository
public class CompetenceService extends GenericService<Competence> {

    @Override
    protected Class<Competence> getObjectClass() {
        return Competence.class;
    }

    @Override
    protected Query getSearchQuery(Competence competence) {
        StringBuffer request = new StringBuffer();
        request.append("from Competence entity");
        request.append(" left join fetch entity.matiere left join fetch entity.matiere.categorie");
        request.append(" where 1 = 1");

        if (ValidationUtils.isNotEmpty(competence.getNom())) {
            request.append(" and upper(entity.nom) like upper(:label_3)");
        }

        if (ValidationUtils.isNotEmpty(competence.getMatiere())
                && ValidationUtils.isNotEmpty(competence.getMatiere().getId())) {
            request.append(" and entity.matiere.id = :label_1");
        }

        if (ValidationUtils.isNotEmpty(competence.getCategorie())
                && ValidationUtils.isNotEmpty(competence.getCategorie().getId())) {
            request.append(" and entity.matiere.categorie.id = :label_2");
        }

        request.append(" order by entity.nom");

        Query query = this.entityManager.createQuery(request.toString());

        if (ValidationUtils.isNotEmpty(competence.getMatiere())
                && ValidationUtils.isNotEmpty(competence.getMatiere().getId())) {
            query.setParameter("label_1", competence.getMatiere().getId());
        }

        if (ValidationUtils.isNotEmpty(competence.getCategorie())
                && ValidationUtils.isNotEmpty(competence.getCategorie().getId())) {
            query.setParameter("label_2", competence.getCategorie().getId());
        }

        if (ValidationUtils.isNotEmpty(competence.getNom())) {
            query.setParameter("label_3", prepareQueryParam(competence.getNom()));
        }

        return query;
    }

    @SuppressWarnings("unchecked")
    public List<Competence> getCompetenceForMatiereId(Long idMatiere) throws ServiceException {
        try {
            String request = "from Competence where matiere.id = :label_1 order by nom";
            Query query = this.entityManager.createQuery(request);
            query.setParameter("label_1", idMatiere);
            return query.getResultList();
        } catch (PersistenceException e) {
            this.logger.error(e.getCause(), e);
            throw new ServiceException(e, "error.db.param", new String[] { e.getCause().getMessage() });
        }
    }

    @SuppressWarnings("unchecked")
    public List<Competence> getCompetenceNotUsedInLivret(Long idLivret, Long idMatiere) throws ServiceException {
        try {
            String sqlQueryString = "select comp from Competence comp"
                    + "	 inner join comp.matiere inner join comp.matiere.categorie, Livret livret"
                    + " where comp not in elements(livret.competences) and livret.id = :idLivret";
            if (ValidationUtils.isNotEmpty(idMatiere)) {
                sqlQueryString += " and comp.matiere.id = :idMatiere";
            }
            sqlQueryString += " order by comp.nom";
            Query query = this.entityManager.createQuery(sqlQueryString);
            query.setParameter("idLivret", idLivret);
            if (ValidationUtils.isNotEmpty(idMatiere)) {
                query.setParameter("idMatiere", idMatiere);
            }
            return query.getResultList();
        } catch (PersistenceException e) {
            this.logger.error(e.getCause(), e);
            throw new ServiceException(e, "error.db.param", new String[] { e.getCause().getMessage() });
        }
    }
}
